********************************************************************************
** 	TITLE:		UK70_results                                                  ** 
**	AUTHOR:	    Philippe Mongrain                                             **
**	DATA:       1918-2019election_results_by_pcon         		              **
**	VERSION:	Stata 16					                                  **
**	DATE:		October 2022  				                                  **
********************************************************************************

* Version control

version 16.0

* Open log file

capture log close
log using "UK70_results.smcl", replace

* Import the dataset

import excel "1918-2019election_results_by_pcon.xlsx", cellrange(B5:X634) sheet(1970) clear

* Reshape

drop G J M P S V

rename B id
rename C constituency_name
rename D county
rename E region
rename F electorate
rename H votes1
rename I share1
rename K votes2
rename L share2
rename N votes3
rename O share3
rename Q votes4
rename R share4
rename T votes5
rename U share5
rename W totalvotes
rename X turnout

reshape long votes share, i(id) j(party_code)  

gen party_name = " "

replace party_name = "Conservative" if party_code == 1 
replace party_name = "Labour" if party_code == 2
replace party_name = "Liberal" if party_code == 3
replace party_name = "SNP/Plaid Cymru" if party_code == 4
replace party_name = "Other" if party_code == 5

drop party_code

gen party_code = .

replace party_code = 1 if party_name == "Conservative"
replace party_code = 2 if party_name == "Labour"
replace party_code = 3 if party_name == "Liberal"
replace party_code = 4 if party_name == "SNP/Plaid Cymru"
replace party_code = 5 if party_name == "Other"
replace party_code = -9999 if region == "Northern Ireland"
 
* Generate rank of parties

gsort constituency_name -votes
bysort constituency_name : gen rank = _n

gen first = party_name if rank == 1
gen second = party_name if rank == 2
gen third = party_name if rank == 3

bysort constituency_name : gen winner70 = first[1]
bysort constituency_name : gen runnerup70 = second[2]
bysort constituency_name : gen thirdplace70 = third[3]

* Generate margins of victory

bysort constituency_name : gen margin = (share[1] - share[2])*100

* Generate effective number of electoral parties

gen sqrvotepr = (share)^2

bysort constituency_name : egen sumsqrvotepr = sum(sqrvotepr)

gen enep = 1/sumsqrvotepr

* Labelling parties

replace party_name = "1" if party_name == "Conservative"
replace party_name = "2" if party_name == "Labour"
replace party_name = "3" if party_name == "Liberal"
replace party_name = "4" if party_name == "SNP/Plaid Cymru"
replace party_name = "5" if party_name == "Other"

destring (party_name), replace

replace winner70 = "1" if winner70 == "Conservative"
replace winner70 = "2" if winner70 == "Labour"
replace winner70 = "3" if winner70 == "Liberal"
replace winner70 = "4" if winner70 == "SNP/Plaid Cymru"
replace winner70 = "5" if winner70 == "Other"

destring (winner70), replace

replace runnerup70 = "1" if runnerup70 == "Conservative"
replace runnerup70 = "2" if runnerup70 == "Labour"
replace runnerup70 = "3" if runnerup70 == "Liberal"
replace runnerup70 = "4" if runnerup70 == "SNP/Plaid Cymru"
replace runnerup70 = "5" if runnerup70 == "Other"

destring (runnerup70), replace

replace thirdplace70 = "1" if thirdplace70 == "Conservative"
replace thirdplace70 = "2" if thirdplace70 == "Labour"
replace thirdplace70 = "3" if thirdplace70 == "Liberal"
replace thirdplace70 = "4" if thirdplace70 == "SNP/Plaid Cymru"
replace thirdplace70 = "5" if thirdplace70 == "Other"

destring (thirdplace70), replace

label define party 1 "Conservative" 2 "Labour" 3 "Liberal" 4 "SNP/Plaid Cymru" 5 "Other"
label values party_name winner70 runnerup70 thirdplace70 party
 
* Selecting, ordering, and sorting variables

keep id constituency_name party_name rank votes share margin enep winner70 runnerup70 thirdplace70

order id constituency_name party_name rank votes share margin enep winner70 runnerup70 thirdplace70

sort id rank

drop if rank > 3

* Clean district names

replace constituency_name = upper(constituency_name)

* Drop duplicates

duplicates tag id, gen(dup)
duplicates drop id, force
drop dup

* Saving file

save UK70_results, replace

* Merge with UK66_results.dta and generate reelection variable

merge 1:1 _n using UK66_results.dta

* Generate variable for incumbent party reelection

gen reelected70 = 1 if winner70 == winner66
replace reelected70 = 0 if reelected70!=1

* Speaker's constituency

replace share = 0.67174369747 if constituency_name == "SOUTHAMPTON,ITCHEN"
replace margin = 45.2959444647 if constituency_name == "SOUTHAMPTON,ITCHEN"
replace enep = 1.95660150622 if constituency_name == "SOUTHAMPTON,ITCHEN"
replace party_name = 2 if constituency_name == "SOUTHAMPTON,ITCHEN"
replace winner70 = 2 if constituency_name == "SOUTHAMPTON,ITCHEN"
replace runnerup70 = . if constituency_name == "SOUTHAMPTON,ITCHEN"
replace thirdplace70 = . if constituency_name == "SOUTHAMPTON,ITCHEN"
replace reelected70 = 1 if constituency_name == "SOUTHAMPTON,ITCHEN"

* Saving file

save UK6670_results, replace

log close